<?php

class Engine_Db_Schema {
	
	protected $exception;
	protected $db;
	
	public function __construct($db) {
		if ($db != null)
			$this->db = $db;
		else 
			$this->db = null;	
		$this->init();
	} 
	
	public function init() {
		
		defined('ACL_ACCESS') 		|| define('ACL_ACCESS', 		TABLE_PREFIX.'acl_access');
		defined('ACL_RESOURCES') 	|| define('ACL_RESOURCES', 		TABLE_PREFIX.'acl_resources');
		defined('ACL_ROLES')		|| define('ACL_ROLES', 			TABLE_PREFIX.'acl_roles');
		defined('ACL_USER_ROLES')	|| define('ACL_USER_ROLES',		TABLE_PREFIX.'acl_user_roles');

		defined('CORE_TYPES')		|| define('CORE_TYPES',			TABLE_PREFIX.'core_types');
		defined('CORE_LISTS')		|| define('CORE_LISTS',			TABLE_PREFIX.'core_lists');
				
		defined('CORE_BLACKLIST')	|| define('CORE_BLACKLIST',		TABLE_PREFIX.'core_blacklist');
		defined('CORE_CONFIG')		|| define('CORE_CONFIG',		TABLE_PREFIX.'core_config');
		defined('CORE_SESSIONS')	|| define('CORE_SESSIONS',		TABLE_PREFIX.'core_sessions');
		
		defined('CORE_MODULES')		|| define('CORE_MODULES',		TABLE_PREFIX.'core_modules');
		defined('CORE_PLUGINS')		|| define('CORE_PLUGINS', 		TABLE_PREFIX.'core_plugins');
		defined('CORE_ROUTES')		|| define('CORE_ROUTES',		TABLE_PREFIX.'core_routes');
		defined('CORE_PAGES')		|| define('CORE_PAGES',			TABLE_PREFIX.'core_pages');
		defined('CORE_HELPERS')		|| define('CORE_HELPERS',		TABLE_PREFIX.'core_helpers');
		defined('CORE_WIDGETS')		|| define('CORE_WIDGETS',		TABLE_PREFIX.'core_widgets');
		defined('CORE_BLOCKS')		|| define('CORE_BLOCKS',		TABLE_PREFIX.'core_blocks');
		defined('CORE_MENUS')		|| define('CORE_MENUS',			TABLE_PREFIX.'core_menus');
						
		defined('CORE_USERS')		|| define('CORE_USERS',			TABLE_PREFIX.'core_users');
	}

	public function getException() {
		return $this->exception;
	}	
	
	protected function insertData() {
		$data = new Engine_Db_SchemaData($this->db);
		$data->insertData();
	}

	public function createTables() {
		$result = false;
		if ($this->db != null) {
			if ($this->dropTables()) {
				$create[] = $this->create_core_types();
				$create[] = $this->create_core_lists();
				$create[] = $this->create_core_blacklist();
				$create[] = $this->create_core_config();
				$create[] = $this->create_core_modules();
				$create[] = $this->create_core_plugins();
				$create[] = $this->create_core_helpers();
				$create[] = $this->create_core_widgets();
				$create[] = $this->create_core_blocks();
				$create[] = $this->create_core_routes();
				$create[] = $this->create_core_sessions();
				$create[] = $this->create_core_users();
				$create[] = $this->create_core_pages();
				$create[] = $this->create_acl_roles();
				$create[] = $this->create_acl_resources();
				$create[] = $this->create_acl_user_roles();
				$create[] = $this->create_acl_access();
				$this->db->beginTransaction();
				try {
					foreach($create as $sql) {
						$this->db->query($sql);
					}
					$this->db->commit();
					$this->insertData(); // INSERT data
					$result = true;
				} catch (Exception $e) {
					$this->db->rollback();
					$this->exception = $e;
				}
			}
		}		
		return $result;
	}
	
	protected function dropTables() {
		$result = false;
		
		$drop[] = "DROP TABLE IF EXISTS ". ACL_ACCESS;
		$drop[] = "DROP TABLE IF EXISTS ". ACL_USER_ROLES;
		$drop[] = "DROP TABLE IF EXISTS ". ACL_RESOURCES;
		$drop[] = "DROP TABLE IF EXISTS ". ACL_ROLES;
			
		$drop[] = "DROP TABLE IF EXISTS ". CORE_BLACKLIST;
		$drop[] = "DROP TABLE IF EXISTS ". CORE_CONFIG;
		$drop[] = "DROP TABLE IF EXISTS ". CORE_SESSIONS;
			
		$drop[] = "DROP TABLE IF EXISTS ". CORE_ROUTES;
		$drop[] = "DROP TABLE IF EXISTS ". CORE_PAGES;
		$drop[] = "DROP TABLE IF EXISTS ". CORE_PLUGINS;
		$drop[] = "DROP TABLE IF EXISTS ". CORE_MODULES;
		$drop[] = "DROP TABLE IF EXISTS ". CORE_HELPERS;
		$drop[] = "DROP TABLE IF EXISTS ". CORE_WIDGETS;
	
		$drop[] = "DROP TABLE IF EXISTS ". CORE_USERS;
		
		$drop[] = "DROP TABLE IF EXISTS ". CORE_LISTS;
		$drop[] = "DROP TABLE IF EXISTS ". CORE_TYPES;
				
		$this->db->beginTransaction();
		try {			
			foreach ($drop as $sql) {
				$this->db->query($sql);
			}
			$this->db->commit();
			$result = true;
		} catch (Exception $e) {
			$this->db-Rollback();
			$this->exception = $e;
		}
		return $result;
	}
	
	protected function create_acl_access() {
		$table = ACL_ACCESS;
		$fk_acl_resources = ACL_RESOURCES;
		$fk_acl_roles = ACL_ROLES;
		$sql = 
		"CREATE TABLE $table (
			access_id BIGINT(10) NOT NULL AUTO_INCREMENT,
			access_role_id BIGINT(10) NOT NULL,
			access_resource_id BIGINT(10) NOT NULL,
			access_allow BOOLEAN NOT NULL DEFAULT FALSE,
		
		  PRIMARY KEY (access_id),
		  UNIQUE KEY UNQ_ACL_ACCESS (access_role_id, access_resource_id),
		  FOREIGN KEY (access_resource_id) REFERENCES $fk_acl_resources (resource_id) ON DELETE CASCADE,
		  FOREIGN KEY (access_role_id) REFERENCES $fk_acl_roles (role_id) ON DELETE CASCADE
		) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000 ;";
		
		return $sql;
	}
	
	protected function create_acl_resources() {
		$table = ACL_RESOURCES;
		$sql = 
		"CREATE TABLE $table (
			resource_id BIGINT(10) NOT NULL AUTO_INCREMENT,
			resource_name VARCHAR(120) NOT NULL,
		
		  PRIMARY KEY (resource_id),
		  UNIQUE KEY UNQ_ACL_NAME (resource_name)
		) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000 ;";
		return $sql;
	}
	
	protected function create_acl_roles() {
		$table = ACL_ROLES;
		$sql = 
		"CREATE TABLE $table (
			role_id BIGINT(10) NOT NULL AUTO_INCREMENT,
			role_name VARCHAR(25) NOT NULL,
		
		  PRIMARY KEY (role_id),
		  UNIQUE KEY UNQ_ACL_ROLE_NAME(role_name)
		) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000 ;";
		return $sql;
	}
	
	protected function create_acl_user_roles() {
		$table = ACL_USER_ROLES;
		$fk_core_users = CORE_USERS;
		$fk_acl_roles = ACL_ROLES;
		$sql = 
		"CREATE TABLE $table (
			user_role_id BIGINT(10) NOT NULL AUTO_INCREMENT,
			user_role_user_id BIGINT(10) NOT NULL,
			user_role_role_id  BIGINT(10) NOT NULL,
			
		  PRIMARY KEY (user_role_id),
		  UNIQUE KEY UNQ_ACL_USER_ROLE(user_role_user_id, user_role_role_id),
		  FOREIGN KEY (user_role_user_id) REFERENCES $fk_core_users (user_id) ON DELETE CASCADE,
		  FOREIGN KEY (user_role_role_id) REFERENCES $fk_acl_roles (role_id) ON DELETE CASCADE
		) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000 ;";
		return $sql;
	}
	
	protected function create_core_blacklist() {
		$table = CORE_BLACKLIST;
		$fk_types = CORE_TYPES;
		$sql = 
		"CREATE TABLE $table(
			blacklist_id BIGINT(10) NOT NULL AUTO_INCREMENT,
			blacklist_type_id VARCHAR(40) NOT NULL,
			blacklist_value VARCHAR(150) NOT NULL,
		
		  PRIMARY KEY (blacklist_id),
		  UNIQUE KEY UNQ_BLACKLIST_VALUE(blacklist_type_id, blacklist_value),
		  FOREIGN KEY (blacklist_type_id) REFERENCES $fk_types(type_id) ON DELETE CASCADE
		) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000 ;";
		return $sql;
	}
	
	protected function create_core_config() {
		$table = CORE_CONFIG;
		$sql =
		"CREATE TABLE $table(
			config_id BIGINT(10) NOT NULL AUTO_INCREMENT,
			config_key VARCHAR(50) NOT NULL,
			config_value TEXT  NOT NULL,
			
		
		  PRIMARY KEY (config_id),
		  UNIQUE KEY UNQ_CONFIG_KEY (config_key)
		) ENGINE=INNODB DEFAULT CHARSET=utf8  AUTO_INCREMENT=1000 ;";
		return $sql;
	}
	
	protected function create_core_sessions() {
		$table = CORE_SESSIONS;
		$sql = 
		"CREATE TABLE $table (
		    session_id VARCHAR(32) NOT NULL,
			session_modified INTEGER NOT NULL,
			session_lifetime INTEGER NOT NULL,
			session_data TEXT NOT NULL,
			session_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
		
		  PRIMARY KEY (session_id)
		) ENGINE=INNODB DEFAULT CHARSET=utf8 ;";
		return $sql;
	}
	
	protected function create_core_modules() {
		$table = CORE_MODULES;
		$sql =
		"CREATE TABLE $table (
		    module_id BIGINT(10) NOT NULL AUTO_INCREMENT,
			module_name VARCHAR(20) NOT NULL,
		    module_layout VARCHAR(20) NOT NULL,      
			module_active BOOL NOT NULL DEFAULT TRUE,
		
		  PRIMARY KEY (module_id),
		  UNIQUE KEY UNQ_MODULE_NAME(module_name)
		) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000 ;";
		return $sql;
	}
	
	protected function create_core_plugins() {
		$table = CORE_PLUGINS;
		$sql =
		"CREATE TABLE $table (
			plugin_id BIGINT(10) NOT NULL AUTO_INCREMENT,
			plugin_classname VARCHAR(60) NOT NULL,
			plugin_name VARCHAR(40) NOT NULL,
			plugin_options TEXT NOT NULL,
			plugin_active BOOLEAN NOT NULL DEFAULT FALSE,
			plugin_stackindex INTEGER NOT NULL,
			
		  PRIMARY KEY (plugin_id),
		  UNIQUE KEY UNQ_PLUGIN_NAME(plugin_name),
		  UNIQUE KEY UNQ_PLUGIN_CLASS_NAME(plugin_classname),
		  UNIQUE KEY UNQ_PLUGIN_STACKINDEX(plugin_stackindex)
		) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000 ;";
		return $sql;
	}
	
	protected function create_core_routes() {
		$table = CORE_ROUTES;
		$fk_core_modules = CORE_MODULES;
		$sql =
		"CREATE TABLE $table (
		    route_id BIGINT(10) NOT NULL AUTO_INCREMENT,
			route_module_id BIGINT(10) NOT NULL,
			route_name VARCHAR(40) NOT NULL,
		    route_uri VARCHAR(255) NOT NULL,    
			route_controller VARCHAR(40) NOT NULL,
			route_action VARCHAR(40) NOT NULL,
			route_active BOOL NOT NULL DEFAULT TRUE,
			
		  PRIMARY KEY (route_id),
		  UNIQUE KEY UNQ_ROUTE_NAME(route_name),
		  FOREIGN KEY (route_module_id) REFERENCES $fk_core_modules (module_id) ON DELETE CASCADE
		) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000 ;";
		return $sql;
	}
	
	protected function create_core_pages() {
		$table = CORE_PAGES;
		$fk_core_modules = CORE_MODULES;
		$fk_core_users = CORE_USERS;
		$sql =
		"CREATE TABLE $table (
			page_id BIGINT(10) NOT NULL AUTO_INCREMENT,
			page_module_id BIGINT(10) NOT NULL,
			page_author_id BIGINT(10) NOT NULL,
			page_uri VARCHAR(255) NOT NULL,
			page_anchor VARCHAR(30) NOT NULL,
			page_title VARCHAR(60) NOT NULL,
			page_description VARCHAR(150) NOT NULL,
			page_keywords VARCHAR(80) NOT NULL,
			page_noindex BOOLEAN NOT NULL DEFAULT FALSE,
			page_nofollow BOOLEAN NOT NULL DEFAULT FALSE,
			page_published BOOLEAN NOT NULL DEFAULT FALSE,
			page_publish_date DATETIME NOT NULL,
			page_content TEXT NOT NULL,
			page_views BIGINT(20) NOT NULL DEFAULT 0,
		
		  PRIMARY KEY (page_id),
		  UNIQUE KEY UNQ_PAGE_URI(page_uri),
		  FOREIGN KEY (page_module_id) REFERENCES $fk_core_modules (module_id) ON DELETE CASCADE,
		  FOREIGN KEY (page_author_id) REFERENCES $fk_core_users (user_id) ON DELETE CASCADE
		) ENGINE=INNODB DEFAULT CHARSET=utf8  AUTO_INCREMENT=1000 ;";
		return $sql;
	}
	
	protected function create_core_users() {
		$table = CORE_USERS;
		$sql =
		"CREATE TABLE $table(
        		user_id BIGINT(10) NOT NULL AUTO_INCREMENT,
				user_username VARCHAR(15) NOT NULL,
				user_firstname VARCHAR(25) NOT NULL,
				user_lastname VARCHAR(30) NOT NULL,
        		user_email VARCHAR(80) NOT NULL,      
				user_password VARCHAR(32) NOT NULL,
				user_verification_code VARCHAR(32) NOT NULL,
				user_last_ip VARCHAR(15) NOT NULL,
				user_login_count INTEGER NOT NULL DEFAULT 0,
				user_active BOOLEAN NOT NULL DEFAULT TRUE,
				user_suspended BOOLEAN NOT NULL DEFAULT FALSE,
				user_create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
				user_update_date TIMESTAMP NOT NULL,
				user_password_date TIMESTAMP NOT NULL,
				user_login_date TIMESTAMP NOT NULL,
				user_params TEXT NOT NULL,
		
		  PRIMARY KEY (user_id),
		  UNIQUE KEY UNQ_USER_USERNAME(user_username),
		  UNIQUE KEY UNQ_USER_EMAIL(user_email)
		) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000 ;";
		return $sql;
	}
	
	
	protected function create_core_helpers() {
		$table = CORE_HELPERS;
		$sql =
		"CREATE TABLE $table(
			helper_id BIGINT(10) NOT NULL AUTO_INCREMENT,
			helper_classname VARCHAR(60) NOT NULL,
			helper_name VARCHAR(40) NOT NULL,
			helper_options TEXT NOT NULL,
			helper_active BOOLEAN NOT NULL DEFAULT FALSE,
			
		  PRIMARY KEY (helper_id),
		  UNIQUE KEY UNQ_HELPER_NAME(helper_name),
		  UNIQUE KEY UNQ_HELPER_CLASS_NAME(helper_classname)
		) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000 ;	";	
		return $sql;
	}
	
	protected function create_core_widgets() {
		$table = CORE_WIDGETS;
		$sql =
		"CREATE TABLE $table(
			widget_id BIGINT(10) NOT NULL AUTO_INCREMENT,
			widget_classname VARCHAR(60) NOT NULL,
			widget_name VARCHAR(40) NOT NULL,
			widget_options TEXT NOT NULL,
			widget_active BOOLEAN NOT NULL DEFAULT FALSE,
			
		  PRIMARY KEY (widget_id),
		  UNIQUE KEY UNQ_WIDGET_NAME(widget_name),
		  UNIQUE KEY UNQ_WIDGET_CLASS_NAME(widget_classname)
		) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000 ;	";	
		return $sql;
	}

	protected function create_core_blocks() {
		$table = CORE_BLOCKS;
		$sql =
		"CREATE TABLE $table(
			widget_id BIGINT(10) NOT NULL AUTO_INCREMENT,
			widget_classname VARCHAR(60) NOT NULL,
			widget_name VARCHAR(40) NOT NULL,
			widget_options TEXT NOT NULL,
			widget_active BOOLEAN NOT NULL DEFAULT FALSE,
			
		  PRIMARY KEY (widget_id),
		  UNIQUE KEY UNQ_WIDGET_NAME(widget_name),
		  UNIQUE KEY UNQ_WIDGET_CLASS_NAME(widget_classname)
		) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000 ;	";	
		return $sql;
	}	

	protected function create_core_types() {
		$table = CORE_TYPES;
		$sql =
		"CREATE TABLE $table (
			type_id VARCHAR(40) NOT NULL,
			type_name VARCHAR(40) NOT NULL,
			type_description VARCHAR (255) NOT NULL,
			
		  PRIMARY KEY (type_id),
		  UNIQUE KEY UNQ_TYPE_NAME(type_id, type_name)
		) ENGINE=INNODB DEFAULT CHARSET=utf8;";	
		return $sql;
	}	
	
	protected function create_core_lists() {
		$table = CORE_LISTS;
		$fk_table = CORE_TYPES;
		$sql =
		"CREATE TABLE $table (
			list_id BIGINT(10) NOT NULL AUTO_INCREMENT,
			list_type_id VARCHAR(40) NOT NULL,
			list_name VARCHAR(40) NOT NULL,
			list_description VARCHAR (255) NOT NULL,
			
		  PRIMARY KEY (list_id),
		  UNIQUE KEY UNQ_LIST_NAME(list_name),
		  FOREIGN KEY (list_type_id) REFERENCES $fk_table(type_id) ON DELETE CASCADE
		) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000 ;";	
		return $sql;
	}	
}

?>